由一次 UPDATE 过慢 SQL 优化而总结出的经验 |
您所在的位置：网站首页 › vba sql语句太长 › 由一次 UPDATE 过慢 SQL 优化而总结出的经验 |
由一次 UPDATE 过慢 SQL 优化而总结出的经验
|
最近，线上的 ETL 数据归档 SQL 发生了点问题，有一个 UPDATE SQL 跑了两天还没跑出来： update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa')这个 SQL 其实就是将 t_retailer_order_record 中 archive_id 为 420a7fe7-4767-45e8-a5f5-72280c192faa 的所有记录的订单 id order_id，对应的订单表中的记录的 archive_id 也更新为 420a7fe7-4767-45e8-a5f5-72280c192faa 并且更新时间保持不变（因为表上有 update_time 按当前时间更新的触发器）。 对于 SQL 的优化，我们可以使用下面三个工具进行分析： EXPLAIN：这个是比较浅显的分析，并不会真正执行 SQL，分析出来的可能不够准确详细。但是能发现一些关键问题。 PROFILING： 通过 set profiling = 1 开启的 SQL 执行采样。可以分析 SQL 执行分为哪些阶段，并且每阶段的耗时如何。需要执行并且执行成功 SQL，并且分析出来的阶段不够详细，一般只能通过某些阶段是否存在如何避免这些阶段的出现进行优化（例如避免内存排序的出现等等）。 OPTIMIZER TRACE：详细展示优化器的每一步，需要执行并且执行成功 SQL。MySQL 的优化器由于考虑的因素太多，迭代太多，配置相当复杂，默认的配置在大部分情况没问题，但是在某些特殊情况会有问题，需要我们进行人为干预。首先，我们针对这个 SQL 进行 EXPLAIN： +----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+ | 1 | UPDATE | t_order_record | NULL | index | NULL | PRIMARY | 8 | NULL | 668618156 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | t_retailer_order_record | NULL | ref | idx_archive_id | idx_archive_id | 195 | const | 1 | 10.00 | Using where | +----+--------------------+-------------------------+------------+-------+----------------+----------------+---------+-------+-----------+----------+-------------+发现 t_order_record 的索引使用有问题，这很奇怪： t_order_record 在 order_id 上面是有索引的，但是这里走的是主键全扫描（主键不是 order_id 而是 id） 子查询中其实只命中了 3 万多条数据。一般出现这种情况，肯定又是 SQL 优化器作妖了。 这也不能完全怪 SQL 优化器我们在日常开发与设计表的时候，很难避免会有一些不合理的使用情况，会有很多索引，可能还会出现 large row。这种千奇百怪的情况中，SQL 优化器需要找到最优的方案确实很难。举一个简单的例子：假设我们有一张表，包含主键 id，有 id = 1 的一条记录，一年后，有了 id = 1000000 的一条记录。然后这时我们同时更新了 id = 1 和 id = 1000000 的记录，那么某个通过其他索引但是命中只有 id = 1 和 id = 1000000 的数据很可能不走索引而是主键搜索。因为最近的更新导致这两条数据跑到了同一页上并且在内存中。 SQL 优化器考虑了很多这种复杂的情况，能在大部分情况下优化 SQL 为更适应当前情况的，但是由于逻辑过于复杂导致某些简单情况下优化的反而很差，这就需要我们根据 OPTIMIZER TRACE 的结果进行手动优化。 使用测试数据库进行 OPTIMIZER TRACE，先分析索引分析前的步骤是否有问题由于 Optimizer_trace 需要 SQL 真正执行，但是这个 SQL 执行不出来了。Optimizer_trace 可以分析优化器的全步骤，我们可以先在一个数据量很少的测试环境，看看在进入统计数据分析前（例如分析索引的离散型数据来决定走哪个索引，这个用测试环境模拟不出来，因为数据和线上肯定有差异，即使复制线上的数据也不行，因为数据在哪些页，索引经过怎样的更新，文件结构和线上不同，统计器的信息肯定不会完全一样），SQL 改写转换是否有问题。 执行： mysql> set session optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.20 sec) mysql> update t_order_record set archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', update_time = update_time where order_id in (select order_id from t_retailer_order_record force index (idx_archive_id) where archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa'); Query OK, 0 rows affected (2.95 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE; steps": [ { "join_preparation": { "select#": 2, "steps": [ { "expanded_query": "/* select#2 */ select `main`.`t_retailer_order_record`.`order_id` from `main`.`t_retailer_order_record` FORCE INDEX (`idx_archive_id`) where (`main`.`t_retailer_order_record`.`archive_id` = '420a7fe7-4767-45e8-a5f5-72280c192faa')" }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": false } }, { "transformation": { "select#": 2, "from": "IN (SELECT)", "to": "EXISTS (CORRELATED SELECT)", "chosen": true, "evaluating_constant_where_conditions": [ ] } } ] } }, { "substitute_generated_columns": { } }, { "condition_processing": { "condition": "WHERE", ## 以下省略通过 Optimizer_trace 我们发现，优化有问题！将 IN 优化成了 EXISTS。这样导致本来我们想的是使用子查询的每一条记录，去匹配外层订单表的记录，变成了遍历外层订单表的每一条记录，去看是否存在于子查询中，这也解释了为啥 explain 的结果是通过主键遍历订单表的每一条记录进行查询。 这个要改的话，只能改变写法来适应，没法通过关闭优化器选项来实现 于是，我们改写并优化 SQL (使用 JOIN，JOIN 是最接近最容易被优化器理解的编写 SQL 的方式)，并且加上了时间条件（我们本身就想只操作 179 天前的数据，这个 archive_id 对应的数据都是 179 天前的），由于订单 id 中本身就带时间（以时间开头，例如 211211094621ord123421 代表 2021 年 12 月 11 日 9 点 46 分 21 秒的一个订单），所以用订单 id 限制时间： UPDATE t_order_record JOIN t_retailer_order_record ON t_order_record.order_id = t_retailer_order_record.order_id SET t_order_record.archive_id = '420a7fe7-4767-45e8-a5f5-72280c192faa', t_order_record.update_time = t_order_record.update_time WHERE t_order_record.order_id |
【本文地址】
今日新闻 |
推荐新闻 |